import sqlite3
from User import User

def delTable(tableName):
    # 删除表
    cursor.execute("DROP TABLE users")

def checkTableExists(tableName):
    sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s'" % tableName
    cursor.execute(sql)
    if cursor.fetchone()[0] == 1:
        print('Table exists.')
        return True
    else:
        print('Table does not exist.')
        return False

    # cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='表名'")
    # if cursor.fetchone():
    #     print("表存在")
    # else:
    #     print("表不存在")

def addUser(user):
    cursor.execute(f"INSERT INTO users (name, age) VALUES ('{user.name}', {user.age})")

def addUsers(*users):
    for user in users:
        cursor.execute(f"INSERT INTO users (name, age) VALUES ('{user.name}', {user.age})")

def createTable():
    # 创建表
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            age INTEGER NOT NULL
        )
    ''')

def showUsers():
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    for row in rows:
        print(row)


def updateUser(id, name, age):
    cursor.execute(f"UPDATE users SET name='{name}', age={age} WHERE id={id}")

def updateUserByName(name, age):
    cursor.execute(f"UPDATE users SET age={age} WHERE name='{name}'")

def deleteUser(id):
    cursor.execute(f"DELETE FROM users WHERE id={id}")

def findUserById(id):
    cursor.execute(f"SELECT * FROM users WHERE id={id}")
    r = cursor.fetchone()
    # 将r转换成User对象
    return User(r[1], r[2])

def findUserByName(name):
    cursor.execute(f"SELECT * FROM users WHERE name='{name}'")
    r = cursor.fetchone()
    # 将r转换成User对象
    return User(r[1], r[2])

def findUsersByAgeGreaterThan(age):
    cursor.execute(f"SELECT * FROM users WHERE age>{age}")
    r = cursor.fetchall()
    # 将r转换成User对象
    return [User(row[1], row[2]) for row in r]

def findUsersByAgeLessThan(age):
    cursor.execute(f"SELECT * FROM users WHERE age<{age}")
    r = cursor.fetchall()
    # 将r转换成User对象
    return [User(row[1], row[2]) for row in r]


if __name__ == '__main__':
    # 连接数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

    b = checkTableExists('users')
    if b == False:
        # 创建表
        createTable()


    # 插入数据
    # cursor.execute("INSERT INTO users (name, age) VALUES ('John Doe', 30)")
    # cursor.execute("INSERT INTO users (name, age) VALUES ('Tom', 31)")
    # cursor.execute("INSERT INTO users (name, age) VALUES ('Jone', 32)")
    # cursor.execute("INSERT INTO users (name, age) VALUES ('Maks', 28)")
    # cursor.execute("INSERT INTO users (name, age) VALUES ('Enges', 25)")
    # cursor.execute("INSERT INTO users (name, age) VALUES ('Edge', 31)")

    # user = User(name='Jone', age=32)
    # addUser(user)

    # 创建多个user
    users = [User(name='Maks', age=28), User(name='Enges', age=25), User(name='Edge', age=31)]
    addUsers(*users)

    # 提交事务
    conn.commit()

    # 查询数据
    showUsers()


    # 关闭连接
    conn.close()